import os
from flask_login import UserMixin
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime

db = SQLAlchemy()

# 数据库连接配置
db_config = {
    'user': os.getenv('DB_USER'),                # 你的数据库用户名
    'password': os.getenv('DB_PASSWORD'),    # 你的数据库密码
    'host': os.getenv('DB_HOST'),           # 据库主机名（一般是 localhost）
    'database': os.getenv('DB_DATABASE')       # 数据库名
}


class User(UserMixin, db.Model):
    __tablename__ = 'users'
    user_id = db.Column(db.Integer, primary_key=True)  # 更改 id 为 user_id
    username = db.Column(db.String(50), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    password_hash = db.Column(db.String(128), nullable=False)
    role = db.Column(db.String(20), nullable=False)  # 添加 role 字段
    created_at = db.Column(
        db.DateTime,
        nullable=False,
        default=db.func.current_timestamp())  # 添加 created_at 字段
    updated_at = db.Column(
        db.DateTime,
        nullable=False,
        default=db.func.current_timestamp(),
        onupdate=db.func.current_timestamp())  # 添加 updated_at 字段

    def get_id(self):
        return str(self.user_id)


class Game(db.Model):
    __tablename__ = 'games'
    game_id = db.Column(db.BigInteger, primary_key=True)
    parent_id = db.Column(db.BigInteger, db.ForeignKey('users.user_id'), nullable=False)
    title = db.Column(db.String(255), nullable=False)
    description = db.Column(db.Text)
    category = db.Column(db.String(100))
    mode = db.Column(db.Enum('spell', 'translate'), nullable=False)
    duration = db.Column(db.Integer, default=300)
    created_at = db.Column(db.DateTime, default=db.func.current_timestamp())
    updated_at = db.Column(
        db.DateTime,
        default=db.func.current_timestamp(),
        onupdate=db.func.current_timestamp())


class Wordlist(db.Model):
    __tablename__ = 'wordlist'
    word_id = db.Column(db.BigInteger, primary_key=True)
    english = db.Column(db.String(100), nullable=False, unique=True)
    chinese = db.Column(db.String(255), nullable=False)
    phonetic = db.Column(db.String(100))
    audio = db.Column(db.String(255))
    example = db.Column(db.Text)
    created_at = db.Column(db.DateTime, default=db.func.current_timestamp())
    updated_at = db.Column(
        db.DateTime,
        default=db.func.current_timestamp(),
        onupdate=db.func.current_timestamp())


class GameWordlist(db.Model):
    __tablename__ = 'game_wordlist'
    id = db.Column(db.BigInteger, primary_key=True)
    game_id = db.Column(db.BigInteger, db.ForeignKey('games.game_id'), nullable=False)
    word_id = db.Column(db.BigInteger, db.ForeignKey('wordlist.word_id'), nullable=False)
    sequence = db.Column(db.Integer, nullable=False)


class GameRecord(db.Model):
    __tablename__ = 'game_records'
    record_id = db.Column(db.BigInteger, primary_key=True)
    game_id = db.Column(db.BigInteger, db.ForeignKey('games.game_id'), nullable=False)
    user_id = db.Column(db.BigInteger, db.ForeignKey('users.user_id'),
                        nullable=False)  # Change child_id to user_id
    game_mode = db.Column(db.Enum('spell', 'translate'), nullable=False)
    score = db.Column(db.Integer, default=0)
    # Change correct_rate to completion_rate
    completion_rate = db.Column(db.Numeric(5, 2), default=0.00)
    status = db.Column(db.Enum('not_started', 'in_progress', 'completed'), default='not_started')
    completed_at = db.Column(db.DateTime, nullable=True)
    created_at = db.Column(db.DateTime, default=db.func.current_timestamp())


class WordLearningRecord(db.Model):
    __tablename__ = 'word_learning_records'
    id = db.Column(db.BigInteger, primary_key=True)
    user_id = db.Column(db.BigInteger, db.ForeignKey('users.user_id'), nullable=False)
    word_id = db.Column(
        db.BigInteger, db.ForeignKey('wordlist.word_id'), nullable=False)
    spell_correct_count = db.Column(db.Integer, default=0)
    spell_wrong_count = db.Column(db.Integer, default=0)
    translate_correct_count = db.Column(db.Integer, default=0)
    translate_wrong_count = db.Column(db.Integer, default=0)
    last_reviewed_at = db.Column(db.DateTime, default=db.func.current_timestamp(),
                                 onupdate=db.func.current_timestamp())

    # Define unique constraint
    __table_args__ = (db.UniqueConstraint('user_id', 'word_id', name='unique_user_word'),)


class UserScores(db.Model):
    __tablename__ = 'user_scores'
    user_id = db.Column(db.BigInteger, db.ForeignKey('users.user_id'), primary_key=True)
    score = db.Column(db.Integer, default=0)
    games = db.Column(db.Integer, default=0)
    spell_count = db.Column(db.Integer, default=0)
    trans_count = db.Column(db.Integer, default=0)
    other_count = db.Column(db.Integer, default=0)
    avg_rate = db.Column(db.Numeric(5, 2), default=0.00)
    updated_at = db.Column(db.DateTime, default=db.func.current_timestamp(),
                           onupdate=db.func.current_timestamp())
